package action;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;

import bean.User;
import util.HibernateUtil;

/**
 * 本类测试HQL查询语言的使用
 * @author 半颗柠檬、
 *
 */
public class Query_HQL {
	public static void main(String[] args) {
		// Query_HQL.testSelectAll();
		// Query_HQL.testSelectpart(); // 查询部分字段的记录
		// Query_HQL.testFunction(); // 执行数据库函数
//		Query_HQL.testPage();
		Query_HQL.test_DML();  
	}


	/**
	 * 查询所有记录
	 */
	private static void testSelectAll() {

		Session session = null;
		Transaction tran = null;
		Query query = null;
		String sql = "";
		try {
			session = HibernateUtil.getSession();
			tran = session.beginTransaction();

			/**
			 * 最简单写法
			 */
			sql = "from User"; // 这里的User 一定要和javabean的类名一致，如果写成user也是错误的
			query = session.createQuery(sql);

			List<User> userList = query.list();

			for (User user : userList) {

				System.out.println("userid==" + user.getUserid() + "\t"
						+ "username=" + user.getUsername() + "\t"
						+ "password==" + user.getPassword());

			}
			/*********************************************/

			/**
			 * 标准写法
			 */
			sql = "select u from User u"; // 这里不能用 select * 否则无法解析
			query = session.createQuery(sql);
			userList = query.list();

			for (User user : userList) {

				System.out.println("userid==" + user.getUserid() + "\t"
						+ "username=" + user.getUsername() + "\t"
						+ "password==" + user.getPassword());

			}
			/*********************************************/

			/**
			 * 加了查询条件
			 */
			sql = "select u from User u where username like '%user%'  and userid in(3,4) ";
			query = session.createQuery(sql);

			userList = query.list();

			for (User user : userList) {

				System.out.println("userid==" + user.getUserid() + "\t"
						+ "username=" + user.getUsername() + "\t"
						+ "password==" + user.getPassword());

			}
			/*********************************************/
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			HibernateUtil.closeSession();

		}
	}

	/**
	 * 查询部分字段的记录
	 */
	private static void testSelectpart() {

		Session session = null;
		Transaction tran = null;
		Query query = null;
		String sql = "";

		try {

			session = HibernateUtil.getSession();
			tran = session.beginTransaction();

			/**
			 * 1. 当查询条件为部分字段的时候，默认返回的是对象数组，而不是javabean对象
			 */
			sql = "select userid,username,password from User";
			query = session.createQuery(sql);

			List<Object[]> objList = query.list();

			for (Object[] userObj : objList) {

				System.out.println("userid==" + userObj[0] + "\t" + "username="
						+ userObj[1] + "\t" + "password==" + userObj[2]);

			}
			/************************************************************/

			/**
			 * 2. 当查询条件为部分字段的时,设置返回一个Map
			 */
			sql = "select new Map(userid as userid,username as username,password as password) from User";
			query = session.createQuery(sql);

			List<Map<String, Object>> userMapList = query.list();

			for (Map<String, Object> rowMap : userMapList) {
				System.out.println(rowMap);

			}
			/************************************************************/

			/*
			 * 3. 当查询条件为部分字段时，返回一个bean，需要在UserBean中定义相应参数个数的构造方法(一般不用这种方法)。
			 */

			sql = "select new bean.User(userid,username,password) from User";
			query = session.createQuery(sql);

			List<User> userList = query.list();
			for (User user : userList) {

				System.out.println("userid1==" + user.getUserid() + "\t"
						+ "username1=" + user.getUsername() + "\t"
						+ "password1==" + user.getPassword());

			}

			/************************************************************/

			/**
			 * 4：默认返回对象数组。将对象数组的数据转换成UserBean/也可以是其他的Bean(RoleBean)
			 */
			sql = "select userid as userid,username as username,password as password from User";
			query = session.createQuery(sql);
			query.setResultTransformer(Transformers.aliasToBean(User.class));// 使用aliasToBean方法
																				// 则sql中的字段要对应bean中的属性，
			// 则“userid as userid” 必须要as 为userid 且Usre中必须有userid

			userList = query.list();

			for (User user : userList) {

				System.out.println("userid2==" + user.getUserid() + "\t"
						+ "username2=" + user.getUsername() + "\t"
						+ "password2==" + user.getPassword());

			}

			/************************************************************/
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			HibernateUtil.closeSession();
		}

	}

	/**
	 * 测试执行数据库函数
	 */
	private static void testFunction() {
		Session session = null;
		Transaction tran = null;
		Query query = null;
		String sql = "";

		try {

			session = HibernateUtil.getSession();
			tran = session.beginTransaction();

			/**
			 * 1.聚合函数
			 */
			sql = "select count(username),min(userid)  from User";
			query = session.createQuery(sql);

			Object[] objArray = (Object[]) query.uniqueResult();

			System.out.println(objArray[0] + "\t" + objArray[1]);

			/**
			 * 2. 字符串函数
			 */
			sql = " select SUBSTRING('Hello World!',1,3) from User where userid=5 ";
			query = session.createQuery(sql);

			List<String> sub_str = query.list();

			for (String obj : sub_str) {

				System.out.println(obj); // 如果sql语句中没有 where userid=5
											// ，则List里有多条记录（等于User表有几条记录）
			}

			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		} finally {
			HibernateUtil.closeSession();
		}
	}

	/**
	 * 测试数据库分页
	 */
	private static void testPage() {
		
		Session session=null;
		Transaction tran=null;
		Query query=null;
		try {
			
			session=HibernateUtil.getSession();
			tran=session.beginTransaction();
			
			String sql=" select a from User a order by userid asc";
			query=session.createQuery(sql);
			
			query.setFirstResult(1);
			query.setMaxResults(4);
			
			List<User> userList=query.list();
			
			for(User user:userList){
				
				System.out.println(user.getUserid()+"\t"+user.getUsername()+"\t");
			}
			
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		}finally{
			HibernateUtil.closeSession();
			
		}

	}

	/**
	 * 测试HQL的DML语句。不能执行DDL语句。 DML： 数据操纵语言，insert、update、delete DDL：数据定义语言，create
	 * 注意： HQL不能执行insert语句，可以执行UPdate/DELETE.
	 * 主要用于批量更新数据。
	 */
	private static void test_DML() {
		Session session=null;
		Transaction tran=null;
		Query query=null;
		String sql="";
		try {
			session=HibernateUtil.getSession();
			tran=session.beginTransaction();
			
			/**
			 *  1. 使用update进行批量更新
			 */
			sql="update User set password='456'   ";
			query=session.createQuery(sql);
			
			int count=query.executeUpdate();
			System.out.println("影响的行数为:"+count);
			
			/**
			 * 不是使用HQL的update批量更新，
			 * 
			 */
			sql="select a from User a order by userid asc";
			query=session.createQuery(sql);
			
			List<User> userList=query.list();
			
			for(User user:userList){
				user.setPassword("1231");
				session.update(user);
				session.flush();
			}
			
			
			tran.commit();
		} catch (Exception e) {
			e.printStackTrace();
			tran.rollback();
		}finally{
			HibernateUtil.closeSession();
		}
		
	}
	
	
}
